ALTER PROCEDURE [dbo].[Report_CategorySameStoreComparisonBySpace]
	@RangeReviewId int
AS
	
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId)
declare @totalCoverage INT = @totalStores * @totalMonths

SELECT
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Width
	, ar.Facings
	, ar.Delist
	, parentCat.Name as Category
	, pl.Name as Location1
	, pla.TypeId as PlanogramType
	, ISNULL(ar.Width, 0) as Width
	, ISNULL(ar.Facings, 0) as Facings
	, ISNULL(ar.Width, 0) * ISNULL(ar.Facings, 0) as LinearSpace
	, CASE WHEN co.IsVATIncluded = 0 THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(Sales / (1 + ar.TaxRate) * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS Sales	
	, CASE WHEN am.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * am.Margin 
		   WHEN scm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * scm.Margin 
		   WHEN cm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * cm.Margin 
		   ELSE ISNULL(SUM(GM * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS GM	
FROM Articles ar 
	INNER JOIN RangeReviews rr on ar.RangeReviewId = rr.Id and rr.Id = @RangeReviewId
	INNER JOIN Categories cat on ar.CategoryId = cat.Id 
	INNER JOIN Categories parentCat on cat.ParentId = parentCat.Id
	INNER JOIN Countries co on rr.CountryId = co.Id
	INNER JOIN Currencies cur on rr.CountryId = cur.CountryId
	LEFT JOIN ArticleSales sales on ar.Id = sales.ArticleId 
		AND (sales.Date BETWEEN @startDate AND @endDate) 
		AND sales.Confirmed = 1 
		AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId)
	LEFT JOIN Spaces spa on spa.ArticleId = ar.Id and spa.RangeReviewId = @RangeReviewId
	LEFT JOIN Planograms pla on spa.PlanogramId = pla.Id
	LEFT JOIN PlanogramLocations pl on pla.PlanogramLocationId = pl.Id	
	LEFT JOIN CurrencyExchangeRates ex on ex.Date = sales.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
	LEFT JOIN TheoreticalMargins am  on am.CountryId = co.Id AND  am.ArticleId = ar.Id  
	LEFT JOIN TheoreticalMargins scm on scm.CountryId = co.Id AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
	LEFT JOIN TheoreticalMargins cm  on cm.CountryId = co.Id AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
Where
	ar.RangeReviewId = @RangeReviewId 
	and parentCat.ParentId = 6
	and ar.IsDeletedPerformance = 0
	and (sales > 0 or (ar.Width * ar.Facings) > 0)
Group by
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Width
	, ar.Facings
	, ar.Delist
	, parentCat.Name
	, pl.Name
	, pla.TypeId
	, co.IsVATIncluded
	, am.Margin
	, scm.Margin
	, cm.Margin
Order By
	Sales DESC